<?php
/**
 * 数据库操作类
 * 宣言 QQ59419979
 */

class Db{
    # 数据库信息
    private static $_instance;
    private $host;      // 服务器地址
    private $user;      // 用户名
    private $pwd;       // 密码   
    private $port;      // 端口
    private $dbname;    // 数据库
    private $charset;   // 字符集
    private $table;     // 数据表
    # 语句条件

    private $field = '*';
    private $where = '';
    private $limit = '';
    private $order = '';
    private $leftJoin = '';
    private $group = '';
    private $onlySql = false;
    /**
     * 构造方法
     */
    private function __construct(){
        $this->host = 'localhost';
        $this->user = 'root';
        $this->pwd  = 'root';
        $this->port = '3306';
        $this->dbname  = 'test';                    // 数据库名
        $this->charset = 'utf8';                    // 连接数据库的编码   可选utf8或GBK'

        
        # 连接数据库
        $this->connect();
        # 选择数据库
        $this->setDb($this->dbname);
        # 设置编码
        $this->setCode();
    }
    private function __clone()
    {
    }
    public static function getDb()
    {
        if (!(self::$_instance instanceof self)) {
            self::$_instance = new self;
        }
        return self::$_instance;
    }
    /**
     * 连接数据库
     */
    private function connect(){
        $this->link = mysqli_connect($this->host.':'.$this->port, $this->user, $this->pwd);
        
        # 检测连接
        if (!$this->link) {
            die("Connection failed: " . mysqli_connect_error());
        }
    }

    /**
     * 选择数据库
     * @param string [name] 数据库名称 默认是构造函数的dbname
     */
    private function setDb($name){
        mysqli_select_db($this->link, $name);
    }

    /**
     * 清除条件语句操作
     */
    private function clearSql(){
        $this->where = '';     
        $this->field = '*';
        $this->limit = '';
        $this->order = '';
        $this->leftJoin = '';
        $this->onlySql  = false;
        $this->group = '';
    }

    /**
     * 设置数据库编码
     */
    public function setCode(){
        mysqli_query($this->link, "set names {$this->charset}");
    }

    /**
     * 语句条件：where
     * @param array [where]  条件数组 键名是字段名
     * @param string[where_add] 附加条件的语句  'aaa' like "a%" || 'id' IN (2,3,4,)
     */
    public function where($where_array, $where_add=''){
        $this->where = 'where ';
        # 循环拼接sql条件
        foreach ($where_array as $key => $value) {
            $this->where .= $key.' = "'. $value .'" and ';
        }
        
        # 如果附加条件条件语句不为空 则拼接进where语句
        if ($where_add !== '') {
            $this->where .= $where_add.' and ';
        }

        $this->where = rtrim($this->where,"and ");
        return $this;
    }

    /**
     * 语句条件：table
     * @param string [name] 要操作的数据表
     */
    public function table($name){
        $this->table = $name;
        return $this;
    }

    /**
     * 语句条件：left join
     */
    public function leftJoin($leftTable, $table_key, $leftTable_key){
        $this->leftJoin = "LEFT JOIN $leftTable ON $this->table.$table_key = $leftTable.$leftTable_key";
        return $this;
    }

    /**
     * 语句条件: field
     * @param string [field] 要获取的字段集合 'a,b,c'
     */
    public function field($field){
        $this->field = $field;
        return $this;
    }

    /**
     * 语句条件: order
     * @param string [order] order语句字符
     */
    public function order($order){
        $this->order = 'order by ' . $order;
        return $this;
    }

    /**
     * 语句条件: limit
     * @param string [limit]  limit的语句
     */
    public function limit($limit,$count){
        $this->limit = 'limit ' . $limit;
        if ( !empty($count) ) {
            $this->limit .= ','.$count;
        }
        return $this;
    }
    /**
     * 语句条件：onlySql
     */
     public function onlySql(){
        $this->onlySql = true;
        return $this;
     }
     /**
      * 语句条件：group 
      */
    public function group($group){
        $this->group = "GROUP BY {$group}";
        return $this;
    }
    /**
     * 检查：是否只输出sql语句
     */
    public function checkOnlySql($sql){
        if( $this->onlySql == true){
            die($sql);
        }
    }

    /**
     * 更新：更新数据
     * @param  array [update]  要更新的新数据 键名为字段名
     * @return mixed 成功返回受影响的的行数  失败返回false
     */
    public function update($update){
        # 把要更新的数据遍历成字符
        $sql_update = '';
        foreach ($update as $key => $value) {
            $sql_update .= $key.'="'.$value.'", ';
        }

        $sql_update = rtrim($sql_update , ', ');
        $sql    = "update {$this->table} set $sql_update {$this->where}";
        $result = mysqli_query($this->link, $sql);
        $this->clearSql(); // 执行完就把sql条件清除

        # 如果成功则返回受影响的行数
        if ( $result == true ) {
            return mysqli_affected_rows($this->link);
        }
        return false;
    }

    /**
     * 增加：添加新数据
     * @param  array [insert] 要新增的数据  键名代表字段名
     * @return mixed 成功则返回新增的id，失败返回false
     */
    public function insert($insert){
        $column  = '';
        $value   = '';
        # 把数组遍历成字符
        foreach ($insert as $key => $v) {
            $column .= $key.', ';
            $value  .= '"'.$v.'",';
        }
        # 去除最后一个逗号
        $column  = rtrim($column, ', ');
        $value   = rtrim($value, ',');
        # 执行插入
        $sql     = "insert into {$this->table} ($column) values ($value)";
        $this->checkOnlySql($sql);
        $result  = mysqli_query($this->link, $sql);

        $this->clearSql(); // 执行完就把sql条件清除

        if ( $result == true ) {
            return mysqli_insert_id($this->link);
        }
        return false;
    }

    /**
     * 删除：删除指定数据
     * @return mixed 成功返回受影响的行数，失败返回错误信息
     */
    public function delete(){
        $sql    = "delete from {$this->table} {$this->where}";
        $this->checkOnlySql($sql);
        $result = mysqli_query($this->link, $sql);
        $this->clearSql(); // 执行完就把sql条件清除

        # 成功则返回受影响的函数
        if ( $result == true ) {
            return mysqli_affected_rows($this->link);
        }
        return false;
    }

    /**
     * 查询: count方法
     * @return string 所差表格的行数
     */
    public function count(){
        $where  = !empty($this->where) ? $this->where : '' ;
        $order  = !empty($this->order) ? $this->order : '' ;
        $group  = !empty($this->group) ? $this->group : '' ;
        $leftJoin = !empty($this->leftJoin) ? $this->leftJoin : '' ;
        $sql    = "SELECT {$this->field} FROM {$this->table} {$leftJoin} {$group} {$where} {$order}";
        $this->checkOnlySql($sql);
        $result = mysqli_query($this->link, $sql);  
        $row    = mysqli_num_rows($result); 

        $this->clearSql(); // 执行完就把sql条件清除

        return $row;
    }
    
    /**
     * 查询：查询一条数据
     * @param
     * @return mixed 成功返回一个一维数组，失败返回FALSE
     */
    public function find(){
        $where  = !empty($this->where) ? $this->where : '' ;
        $order  = !empty($this->order) ? $this->order : '' ;
        $limit  = !empty($this->limit) ? $this->limit : '' ;
        $group  = !empty($this->group) ? $this->group : '' ;
        $leftJoin = !empty($this->leftJoin) ? $this->leftJoin : '' ;
        $sql    = "SELECT {$this->field} FROM {$this->table} {$leftJoin} {$group} {$where} {$order} {$limit}";
        $this->checkOnlySql($sql);
        $result = mysqli_query($this->link, $sql);

        $this->clearSql(); // 执行完就把sql条件清除

        # 判断返回
        if(mysqli_num_rows($result)){
            $row = mysqli_fetch_assoc($result);
            return $row;
        }

        return false;
    }

    /**
     * 查询：查询所有数据
     * @param
     * @return mixed，成功返回一个二维数组，失败返回FALSE
     */
    public function select(){
        $where  = !empty($this->where) ? $this->where : '' ;
        $order  = !empty($this->order) ? $this->order : '' ;
        $limit  = !empty($this->limit) ? $this->limit : '' ;
        $group  = !empty($this->group) ? $this->group : '' ;
        $leftJoin = !empty($this->leftJoin) ? $this->leftJoin : '' ;
        $sql    = "SELECT {$this->field} FROM {$this->table} {$leftJoin} {$group} {$where} {$order} {$limit}";
        $this->checkOnlySql($sql);
        $result = mysqli_query($this->link, $sql);

        $this->clearSql(); // 执行完就把sql条件清除
        if ( $result == false ) {
            return false;
        }
        # 判断返回
        if(mysqli_num_rows($result)){
            $list = array();
            # 遍历
            while($row = mysqli_fetch_assoc($result)){
                $list[] = $row;
            }
            return $list;
        }

        return false;
    }
    /**
     * 执行sql语句
     * @param string sql 要执行的语句
     */
    public function query($sql){
        $this->checkOnlySql($sql);
        return mysqli_query($this->link, $sql);
    }

    /**
     * 析构函数
     */
    public function __destruct(){
        mysqli_close($this->link);
    }
}
